package com.varian.mybatis.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.varian.mybatis.base.PageQuery;
import lombok.experimental.UtilityClass;

import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author ben
 * @since 2024/6/23
 */
@UtilityClass
public class PageUtil {

    private final String SQL_SYNTAX = "(insert|delete|update|select|create|drop|truncate|grant|alter|deny|revoke|call|execute|exec|declare|show|rename|set)" +
            "\\s+.*(into|from|set|where|table|database|view|index|on|cursor|procedure|trigger|for|password|union|and|or)|(select\\s*\\*\\s*from\\s+)|(and|or)\\s+.*(like|=|>|<|in|between|is|not|exists)";

    /**
     * SQL语法检查正则：符合两个关键字（有先后顺序）才算匹配
     */
    private final Pattern SQL_SYNTAX_PATTERN = Pattern.compile(SQL_SYNTAX, Pattern.CASE_INSENSITIVE);
    /**
     * 使用'、;或注释截断SQL检查正则
     */
    private final Pattern SQL_COMMENT_PATTERN = Pattern.compile("'.*(or|union|--|#|/\\*|;)", Pattern.CASE_INSENSITIVE);

    private final String ASC_SUFFIX = "_asc";
    private final String DESC_SUFFIX = "_desc";
    private final Pattern UNDERLINE_PATTERN = Pattern.compile("^(?<column>\\w+)_(?<action>\\w+)$", Pattern.CASE_INSENSITIVE);


    /**
     * 检查参数是否存在 SQL 注入
     *
     * @param value 检查参数
     * @return true 非法 false 合法
     */
    public boolean check(String value) {
        Objects.requireNonNull(value);
        // 处理是否包含SQL注释字符 || 检查是否包含SQL注入敏感字符
        return SQL_COMMENT_PATTERN.matcher(value).find() || SQL_SYNTAX_PATTERN.matcher(value).find();
    }

    /**
     * 删除sql注入
     *
     * @param sql sql
     * @return 删除注入的sql之后的字符串
     */
    public String removeSqlInjection(String sql) {
        return StrUtil.isNotBlank(sql) ? sql.replaceAll(SQL_SYNTAX, StrUtil.EMPTY) : StrUtil.EMPTY;
    }

    /**
     * 刪除字段转义符单引号双引号
     *
     * @param text 待处理字段
     * @return {@link String}
     */
    public String removeEscapeCharacter(String text) {
        return text.replaceAll("\"", "").replaceAll("'", "");
    }

    /**
     * 生成Wrapper对象
     *
     * @param pageQuery 分页查询对象
     * @param <T>       泛型类型
     * @return Wrapper对象
     */
    public <T> Wrapper<T> wrapper(PageQuery pageQuery) {
        QueryWrapper<T> wrapper = Wrappers.query();
        if (CollUtil.isNotEmpty(pageQuery.getOrderByList())) {
            resolveOrderBy(wrapper, pageQuery.getOrderByList());
        }
        if (CollUtil.isNotEmpty(pageQuery.getConditions())) {
            resolveCondition(wrapper, pageQuery.getConditions());
        }
        return wrapper;
    }

    /**
     * 解析条件，对QueryWrapper对象进行条件过滤
     *
     * @param wrapper    QueryWrapper对象
     * @param conditions 条件Map对象，键值对分别为字段和值
     * @param <T>        泛型类型
     */
    private <T> void resolveCondition(QueryWrapper<T> wrapper, Map<String, String> conditions) {
        conditions.forEach((k, v) -> {
            String removedKey = removeSqlInjection(k);
            String removedValue = removeSqlInjection(v);
            // key和value都不为空且key包含下划线
            if (StrUtil.isAllNotEmpty(removedValue, removedValue)
                    && StrUtil.contains(removedKey, StrUtil.UNDERLINE)) {
                Matcher matcher = UNDERLINE_PATTERN.matcher(removedKey);
                if (matcher.matches()) {
                    String column = StrUtil.toUnderlineCase(matcher.group("column"));
                    switch (matcher.group("action")) {
                        case "ne" -> wrapper.ne(column, removedValue);
                        case "eq" -> wrapper.eq(column, removedValue);
                        case "lt" -> wrapper.lt(column, removedValue);
                        case "le" -> wrapper.le(column, removedValue);
                        case "gt" -> wrapper.gt(column, removedValue);
                        case "ge" -> wrapper.ge(column, removedValue);
                        case "notLike" -> wrapper.notLike(column, removedValue);
                        case "like" -> wrapper.like(column, removedValue);
                        case "likeLeft" -> wrapper.likeLeft(column, removedValue);
                        case "likeRight" -> wrapper.likeRight(column, removedValue);
                        default -> {}
                    }
                }
            }
        });
    }

    /**
     * 通过给定的QueryWrapper和排序字段列表解析排序规则
     *
     * @param wrapper  QueryWrapper对象
     * @param orderBys 排序字段列表
     * @param <T>      泛型类型
     */
    private <T> void resolveOrderBy(QueryWrapper<T> wrapper, List<String> orderBys) {
        orderBys.forEach(orderBy -> {
            String str = removeSqlInjection(orderBy);
            if (StrUtil.endWith(str, DESC_SUFFIX)) {
                wrapper.orderByDesc(removeOrderSuffix(orderBy, DESC_SUFFIX));
            } else if (StrUtil.endWith(str, ASC_SUFFIX)) {
                wrapper.orderByAsc(removeOrderSuffix(orderBy, ASC_SUFFIX));
            }
        });
    }

    /**
     * 私有方法，用于去除排序方式中的后缀
     *
     * @param orderBy 排序方式
     * @param suffix  后缀字符串
     * @return 去除后缀后的排序方式
     */
    private String removeOrderSuffix(String orderBy, String suffix) {
        return StrUtil.toUnderlineCase(StrUtil.removeSuffix(orderBy, suffix));
    }

    /**
     * 封装Page对象，转换成指定类型
     *
     * @param page Page对象
     * @param func 转换函数
     * @param <P>  Page泛型
     * @param <R>  转换后泛型
     * @return Page对象
     */
    public <P, R> Page<R> convert(Page<P> page, Function<P, R> func) {
        return (Page<R>) page.convert(func);
    }
}
